﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.IO;
using System.Data;
using System.Data.OleDb;


namespace WebApplication1
{
    public partial class GirdView_Execl_Demo : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        /// <summary>
        /// 上传Execl文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnReadExcel_Click(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile)
            {
                //上传Excel文件
                string fileName = FileUpload1.FileName; //文件全名
                string extName = fileName.Substring(fileName.LastIndexOf(".") + 1); //扩展名（文件类型）
                if (extName.ToLower() == "xls" || extName.ToLower() == "xlsx")
                {
                    string uploadPath = Server.MapPath(@"upload");
                    if (!Directory.Exists(uploadPath))
                    {
                        Directory.CreateDirectory(uploadPath);
                    }
                    FileUpload1.SaveAs(uploadPath + "\\" + fileName);
                    //读取Excel文件
                    string xlsConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;data source={0};Extended Properties=Excel 8.0;", Server.MapPath("upload\\" + fileName));
                    using (OleDbConnection conn = new OleDbConnection(xlsConn))
                    {
                        OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", conn);
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        GridView1.DataSource = ds.Tables[0];
                        GridView1.DataBind();
                    }
                }
                else
                {
                    Response.Write("文件格式错误，只能上传Excel 2003或者2007（扩展名为 .xls或者.xlsx）的文件！");
                    return;
                }
            }

        }

        /// <summary>
        /// 将GridView的数据输出到Excel文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnExportToExcel_Click(object sender, EventArgs e)
        {
            if (GridView1.Rows.Count == 0)
            {
                return;
            }
            Response.ClearContent();
            Response.ContentEncoding = System.Text.Encoding.Default;
            Response.AddHeader("content-disposition", "attatchment;filename=" + HttpUtility.UrlEncode("我的Excel.xls", System.Text.Encoding.UTF8));
            Response.ContentType = "application/vnd.ms-excel";

            //Extension MIME Type
            //.xlsx   application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
            //.xltx   application/vnd.openxmlformats-officedocument.spreadsheetml.template
            //.potx   application/vnd.openxmlformats-officedocument.presentationml.template
            //.ppsx   application/vnd.openxmlformats-officedocument.presentationml.slideshow
            //.pptx   application/vnd.openxmlformats-officedocument.presentationml.presentation
            //.sldx   application/vnd.openxmlformats-officedocument.presentationml.slide
            //.docx   application/vnd.openxmlformats-officedocument.wordprocessingml.document
            //.dotx   application/vnd.openxmlformats-officedocument.wordprocessingml.template
            //.xlam   application/vnd.ms-excel.addin.macroEnabled.12
            //.xlsb   application/vnd.ms-excel.sheet.binary.macroEnabled.12


            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    GridView1.RenderControl(htw);
                    Response.Write(sw.ToString());
                    Response.End();
                }
            }
        }

        //必须重载此方法，以便支持上面的导出操作
        public override void VerifyRenderingInServerForm(Control control)
        {

        }

    }
}